package com.zhentao.service;

import com.zhentao.pojo.DataCollection;
import com.zhentao.dto.SummaryQueryResultDto;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * Excel导出服务
 * 用于生成标准的Excel表格格式
 */
@Service
public class ExcelExportService {
    
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
    /**
     * 导出数据查询结果为Excel
     */
    public byte[] exportDataQueryToExcel(List<DataCollection> dataList) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("处理表数据");
            
            // 创建表头样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {
                "ID", "数据类型", "关区", "报关单号", "商品项号", "货币类型", 
                "货值(人民币)", "货值(美元)", "商品数量", "状态", "创建时间", "操作人", "进出口类型"
            };
            
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
                sheet.setColumnWidth(i, 15 * 256); // 设置列宽
            }
            
            // 填充数据
            for (int i = 0; i < dataList.size(); i++) {
                DataCollection data = dataList.get(i);
                Row row = sheet.createRow(i + 1);
                
                row.createCell(0).setCellValue(data.getId());
                row.createCell(1).setCellValue(getDataTypeLabel(data.getDataType()));
                row.createCell(2).setCellValue(data.getCustomsArea());
                row.createCell(3).setCellValue(data.getDeclareNo());
                row.createCell(4).setCellValue(data.getItemNo());
                row.createCell(5).setCellValue(getCurrencyTypeLabel(data.getCurrencyType()));
                row.createCell(6).setCellValue(data.getAmountCny() != null ? data.getAmountCny() : 0.0);
                row.createCell(7).setCellValue(data.getAmountUsd() != null ? data.getAmountUsd() : 0.0);
                row.createCell(8).setCellValue(data.getGoodsQuantity() != null ? data.getGoodsQuantity() : 0);
                row.createCell(9).setCellValue(getStatusLabel(data.getStatus()));
                row.createCell(10).setCellValue(data.getCreateTime() != null ? DATE_FORMAT.format(data.getCreateTime()) : "");
                row.createCell(11).setCellValue(data.getUserName() != null ? data.getUserName() : "未知用户");
                row.createCell(12).setCellValue(data.getImportExportType());
            }
            
            // 导出为字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
    
    /**
     * 导出汇总查询结果为Excel
     */
    public byte[] exportSummaryQueryToExcel(List<SummaryQueryResultDto> dataList) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("汇总查询");
            
            // 创建表头样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {
                "维度值", "报关单数量", "记录数量", "货值(人民币)", "货值(美元)", "商品数量"
            };
            
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
                sheet.setColumnWidth(i, 20 * 256);
            }
            
            // 填充数据
            for (int i = 0; i < dataList.size(); i++) {
                SummaryQueryResultDto data = dataList.get(i);
                Row row = sheet.createRow(i + 1);
                
                // 维度值 - 转换为中文格式
                String dimensionValues = "";
                if (data.getDimensionValues() != null) {
                    StringBuilder sb = new StringBuilder();
                    data.getDimensionValues().forEach((key, value) -> {
                        String chineseKey = getDimensionChineseName(key);
                        sb.append(chineseKey).append(":").append(value).append(" ");
                    });
                    dimensionValues = sb.toString().trim();
                }
                row.createCell(0).setCellValue(dimensionValues);
                row.createCell(1).setCellValue(data.getDeclareCount());
                row.createCell(2).setCellValue(data.getRecordCount());
                row.createCell(3).setCellValue(data.getAmountCny() != null ? data.getAmountCny().doubleValue() : 0.0);
                row.createCell(4).setCellValue(data.getAmountUsd() != null ? data.getAmountUsd().doubleValue() : 0.0);
                row.createCell(5).setCellValue(data.getGoodsQuantity());
            }
            
            // 导出为字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
    
    /**
     * 创建表头样式
     */
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
    
    /**
     * 数据类型转换
     */
    private String getDataTypeLabel(Integer dataType) {
        if (dataType == null) return "";
        switch (dataType) {
            case 1: return "FTP导入";
            case 2: return "CSV上报";
            case 3: return "手工录入";
            case 4: return "主动采集";
            default: return String.valueOf(dataType);
        }
    }
    
    /**
     * 货币类型转换
     */
    private String getCurrencyTypeLabel(Integer currencyType) {
        if (currencyType == null) return "";
        switch (currencyType) {
            case 1: return "人民币";
            case 2: return "美元";
            default: return String.valueOf(currencyType);
        }
    }
    
    /**
     * 状态转换
     */
    private String getStatusLabel(Integer status) {
        if (status == null) return "";
        switch (status) {
            case 1: return "待入库";
            case 2: return "已入库";
            case 3: return "异常";
            default: return String.valueOf(status);
        }
    }
    
    /**
     * 维度名称转换为中文
     */
    private String getDimensionChineseName(String dimensionKey) {
        if (dimensionKey == null) return "";
        switch (dimensionKey) {
            case "customs_area": return "关区";
            case "declare_no": return "报关单号";
            case "item_no": return "商品项号";
            case "import_export_type": return "进出口类型";
            // 暂时注释掉可能不存在的字段
            /*
            case "enterprise_name": return "企业名称";
            case "enterprise_code": return "企业代码";
            case "enterprise_credit_level": return "企业信用等级";
            case "hs_code": return "HS编码";
            case "goods_name": return "商品名称";
            case "country_code": return "国别代码";
            case "country_name": return "国别名称";
            case "port_code": return "口岸代码";
            case "port_name": return "口岸名称";
            case "transport_mode": return "运输方式";
            */
            default: return dimensionKey;
        }
    }
} 
 
 
 